package cn.edu.sdjzu.xg.kcsj.security;

import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class RoleDao {
    //构造器私有化
    private RoleDao() {
    }

    private static RoleDao roleDao = null;

    //获得实例的方法
    public static RoleDao getInstance() {
        if (roleDao == null) {
            roleDao = new RoleDao();
        }
        return roleDao;
    }


    //查找全部Role
    public Collection<Role> findAll(List<Condition> conditionList, Pagination pagination, Connection connection) throws SQLException {
        int totalNum = CommonDao.getInstance().count(connection, "role");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM role ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null && conditionList.size() > 0) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if (pagination != null) {
            String LimitClause = pagination.toLimitClause(totalNum);
            sql_select.append(LimitClause);
        }
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //创建集合盛放role对象
        Collection<Role> roles = new TreeSet<Role>();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            Collection<Resource> resources = ResourceAssRoleService.getInstance().findResourcesByRoleId(id);
            roles.add(new Role(id, description, resources));
        }
        JdbcHelper.close(resultSet, pstmt);
        return roles;
    }

    public Collection<Role> findAll(Connection connection) throws SQLException {
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM role ");
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        //创建集合盛放role对象
        Collection<Role> roles = new TreeSet<Role>();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            Collection<Resource> resources = ResourceAssRoleService.getInstance().findResourcesByRoleId(id);
            roles.add(new Role(id, description, resources));
        }
        JdbcHelper.close(resultSet, pstmt);
        return roles;
    }

    //查找一个role
    public Role find(Integer id, Connection connection) throws SQLException {
        Role role = null;
        String select_sql = "SELECT * FROM role WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(select_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //创建结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //用结果集中的字段为user对象的各字段赋值
            String description = resultSet.getString("description");
            Collection<Resource> resources = ResourceAssRoleService.getInstance().findResourcesByRoleId(id);
            role = new Role(id, description, resources);
        }
        JdbcHelper.close(resultSet, pstmt);
        return role;
    }


    // 更新role
    public boolean update(Role role, Connection connection) throws SQLException {
        String update_sql = "UPDATE role SET description = ?" + "WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(update_sql);
        //为预编译参数赋值
        pstmt.setString(1, role.getDescription());
        pstmt.setInt(2, role.getId());
        //判断是否修改成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    // 删除role
    public boolean delete(Integer id, Connection connection) throws SQLException {
        String delete_sql = "DELETE FROM role WHERE id = ?";
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(delete_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //判断是否删除成功
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }

    // 增加role
    public Boolean add(Role role, Connection connection) throws SQLException {
        //在连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(
                "INSERT INTO role (description)" +
                        "VALUES(?)");
        //为预编译参数赋值
        pstmt.setString(1, role.getDescription());
        int effectedRow = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return effectedRow > 0;
    }
}
